package com.starbaba.stat.service.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.base.common.database.BaseDao;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.stat.model.StatActivitySummaryModel;
import com.starbaba.stat.service.StatNewActivitySummaryService;
import com.starbaba.stat.utils.Constant;
import com.tuanzi.server.loan_base.model.db.LoanProductInfo;
import com.tuanzi.server.loan_base.model.db.stat.Stat_Activity_Summary_New;


@SuppressWarnings("rawtypes")
@Component
public class StatNewActivitySummaryServiceImpl implements StatNewActivitySummaryService {

	static BaseDao dao = new BaseDao(Constant.LOAN_STAT_DB_POOL);// Constant.LOAN_STAT_DB_POOL
//	static BaseDao dao = new BaseDao("stat");  //正式
	
	@Override
	public void showPage(Map<String, Object> paramsMap) throws Exception {
		List<StatActivitySummaryModel> prdList = dao.list("select a.prd_id, b.name prd_name from (select prd_id from stat.stat_activity_summary group by prd_id order by null )a,loan.comm_product_prdid b where a.prd_id = b.prdid ", StatActivitySummaryModel.class);
		List<StatActivitySummaryModel> channelList = dao.list("select a.channel, b.channel_name from (select channel from stat.stat_activity_summary group by channel order by null )a,loan.comm_product_channel b where a.channel = b.channel_code ", StatActivitySummaryModel.class);
		List<LoanProductInfo> activityList = dao.list(new LoanProductInfo(), new String[0]);
		paramsMap.put("prdList", prdList);
		paramsMap.put("channelList", channelList);
		paramsMap.put("activityList", activityList);
	}

	@Override
	public List getList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prodId = (String) paramsMap.get("prodId");
		String channelId = (String) paramsMap.get("channelId");
		String surroundings = (String) paramsMap.get("surroundings");
		String userType = (String) paramsMap.get("userType");
		String activityId = (String) paramsMap.get("activityId");
		String source = (String) paramsMap.get("source");
		
		StringBuilder whereParams = new StringBuilder("where 1 = 1 ");
		
		if("0".equals(activityId)){  //全部活动
			whereParams.append(" and activity_id = " + 0 +" ");
		} else if(StringUtils.isNotBlank(activityId)){
			whereParams.append("and activity_id = " + activityId + " ");
		} else {
			whereParams.append("and activity_id != 0 ");
		}
		
		if (StringUtils.isNotEmpty(startTime)) {
			whereParams.append("and time >= '" + startTime + "' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			whereParams.append("and time <= '" + endTime + "' ");
		}
		
		if (!"0".equals(platform) && StringUtils.isNotBlank(platform)) { //非选择全部
			whereParams.append("and platform = '"+platform+"' ");
		} 
		
		if (!"0".equals(prodId) && StringUtils.isNotBlank(prodId)) {
			whereParams.append("and prod_id = '" + prodId + "' ");
		}
		
		if (!"0".equals(channelId) && StringUtils.isNotBlank(channelId)) {
			whereParams.append("and channel_id = '" + channelId + "' ");
		}
		
		if (!"0".equals(surroundings) && StringUtils.isNotBlank(surroundings)) {
			whereParams.append("and surroundings = "+surroundings+" ");
		} 
		
		if (StringUtils.isNotEmpty(userType) && StringUtils.isNotBlank(userType)) {
			whereParams.append("and user_type = '"+userType+"' ");
		} 
		
		if (!"0".equals(source) && StringUtils.isNotBlank(source)) {
			whereParams.append("and source = "+source+" ");
		} 
		
		String groupByString = "group by n.time,n.activity_id ";
		if(StringUtils.isEmpty(platform)){  //请选择
			groupByString+=" ,n.platform ";
		}
		if(StringUtils.isEmpty(prodId)){  //请选择
			groupByString+=" ,n.prod_id ";
		}
		if(StringUtils.isEmpty(channelId)){  //请选择
			groupByString+=" ,n.channel_id ";
		}
		if(StringUtils.isEmpty(surroundings)){  //请选择
			groupByString+=" ,n.surroundings ";
		}
		if(StringUtils.isEmpty(source)){  //请选择
			groupByString+=" ,n.source ";
		}
		
		StringBuilder sqlList = new StringBuilder();
		sqlList.append("SELECT n.time time,n.prod_id ,p.name prod_name,n.platform ,n.channel_id ,c.channel_name channel_name,n.surroundings ,n.user_type ,"
				+ "n.activity_id ,n.activity_name ,n.source ,sum(n.details_uv) details_uv ,sum(n.join_uv) join_uv,"
				+ "sum(n.api_h5_uv) api_h5_uv,sum(n.request_union_uv) request_union_uv,sum(n.union_success_sum) union_success_sum,"
				+ "sum(n.union_success_api_sum) union_success_api_sum,sum(n.request_api_uv) request_api_uv,"
				+ "sum(n.submit_order_uv) submit_order_uv,sum(n.submit_order_pv) submit_order_pv "
				+ "FROM stat.stat_activity_summary_new n left outer join loan.comm_product_channel c on n.channel_id = c.channel_code "
				+ "left outer join loan.comm_product_prdid p on n.prod_id = p.prdid ");
		sqlList.append(whereParams);
		sqlList.append(groupByString);
		
		String sqlCount = "select count(1) from (" + sqlList.toString() + ") b ";
		if (start != null && length != null) {
			sqlList.append("order by time desc limit " + start + ", " + length);
		} else {
			sqlList.append("order by time desc");
		}
		
		int count = dao.getInt(sqlCount);
		List<Stat_Activity_Summary_New> list = dao.list(sqlList.toString(), Stat_Activity_Summary_New.class);
		for (Stat_Activity_Summary_New model : list) {
			if(model.getActivity_id() == 0){
				model.setActivity_id(0);
				model.setActivity_name("全部");
			}
			if("0".equals(platform)){
				model.setPlatform("全部");
			}
			if("0".equals(prodId)){
				model.setProd_id(0);
				model.setProd_name("全部");
			}
			if("0".equals(channelId)){
				model.setChannel_id(0);
				model.setChannel_name("全部");
			}
			if("0".equals(surroundings)){
				model.setSurroundings(0);
			}
			if("all".equals(userType)){
				model.setUser_type("全部");
			}
			if("all".equals(model.getUser_type())){
				model.setUser_type("全部");
			}
			if("0".equals(source)){
				model.setSource("0");
			}
		}
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		return list;
	}
	
	@Override
	public  String getExportString(List<Stat_Activity_Summary_New> list) {
		StringBuilder builder = new StringBuilder();
		builder.append("日期,平台,产品,渠道,环境,用户类型,活动id,活动名称,来源,详情UV,参与UV,访问甲方H5UV,访问联登人数,联登成功人数,联登成功甲方数,访问API的UV,提交订单UV,提交订单PV\r\n");
		for (Stat_Activity_Summary_New vo : list) {
			builder.append(vo.getTime()).append(","); 
			builder.append(vo.getPlatform()).append(","); 
			builder.append(vo.getProd_name()).append(","); 
			builder.append(vo.getChannel_name()).append(",");
			
			if(vo.getSurroundings() == 1){
				builder.append("推广H5").append(",");
			}else if(vo.getSurroundings() == 2){
				builder.append("客户端").append(",");
			}else {
				builder.append("全部").append(",");
			}
			
			if ("new".equals(vo.getUser_type())) {
				builder.append("新用户").append(",");
			} else if ("old".equals(vo.getUser_type())) {
				builder.append("老用户").append(",");
			} else {
				builder.append("全部").append(",");
			}
			
			builder.append(vo.getActivity_id()).append(","); 
			builder.append(vo.getActivity_name()).append(","); 
			
			if("1".equals(vo.getSource())){
				builder.append("常规").append(",");
			}else if("2".equals(vo.getSource())){
				builder.append("直接访问").append(",");
			}else if("3".equals(vo.getSource())){
				builder.append("未知").append(",");
			}else{
				builder.append("全部").append(",");
			}
			
			builder.append(vo.getDetails_uv()).append(","); 
			builder.append(vo.getJoin_uv()).append(","); 
			builder.append(vo.getApi_h5_uv()).append(","); 
			builder.append(vo.getRequest_union_uv()).append(",");
			builder.append(vo.getUnion_success_sum()).append(",");
			builder.append(vo.getUnion_success_api_sum()).append(",");
			builder.append(vo.getRequest_api_uv()).append(",");
			builder.append(vo.getSubmit_order_uv()).append(",");
			builder.append(vo.getSubmit_order_pv()).append(",");  
		
			builder.append("\r\n");
		}
		return builder.toString();
	}
	
}





